package com.cpimex.eprice.struts;

import com.cpimex.eprice.Grupo;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForward;
import javax.sql.DataSource;
import java.sql.*;
import javax.naming.InitialContext;
import com.cpimex.eprice.usuarios.Usuario;
import javax.servlet.http.HttpSession;
import org.apache.struts.actions.DispatchAction;
import java.util.*;
import com.cpimex.eprice.Parametro;
import com.cpimex.eprice.ClienteCritico;
import com.cpimex.eprice.ClaseProducto;
import com.cpimex.eprice.Estatus;
import com.cpimex.eprice.bean.EstatusBean;
import com.cpimex.eprice.MatrizCorreo;
import com.cpimex.eprice.bean.GrupoBeanComb;
import com.cpimex.eprice.bean.OtroCorreoBean;
import com.cpimex.eprice.bean.ClienteBean;
import com.cpimex.eprice.Producto;
import com.cpimex.eprice.PrecioLista;
import com.cpimex.eprice.forms.PrecioListaEd;
import org.apache.struts.action.*;
import com.cpimex.eprice.Notificacion;
import com.cpimex.eprice.bean.NotificacionOtroC;
import com.cpimex.eprice.Subsegmento;
import javax.servlet.ServletContext;
import com.cpimex.eprice.Solicitud;
import com.cpimex.eprice.bean.ClienteSegmento;

public class Catalogo extends DispatchAction {
	
	/**
	 * Autorizcion de solicitud multiple en listado de solicitudes  
	 * 
	 * @author  Fernando Celis
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 * @throws Exception
	 */
    public ActionForward autorizarSolicitudMultiple( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response )throws Exception {
        if (!validaSesion(request))return mapping.findForward("sesionInvalida");
        Connection conn = null;
        Connection conn_informix = null;
        String str_mensaje;
        try {

            HttpSession session = request.getSession();

            InitialContext context = new InitialContext();
            DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
            conn = dataSrc.getConnection();

            DataSource dataSrc_informix = (DataSource) context.lookup("java:BaaN");
            conn_informix = dataSrc_informix.getConnection();

            int int_accion = Integer.parseInt( request.getParameter("accion") );
            String str_solicitudes = request.getParameter("detSol");
            String [] strs_idSolicitudes = str_solicitudes.split(",");
            Usuario usr_logeado = (Usuario) session.getAttribute("usuario");

            for( int int_i = 1; int_i < strs_idSolicitudes.length; int_i++ ){
            	
                int int_idDetSol = Integer.parseInt(strs_idSolicitudes[int_i].trim());
                
                String str_perfil = usr_logeado.getStr_perfilActual();
                String str_producto = Solicitud.getProducto(conn.createStatement(), int_idDetSol);
                
                boolean bool_director = Estatus.autorizaDirector(conn.createStatement(), str_producto.trim());
                boolean bol_VPO = Solicitud.autorizaraVPO(conn.createStatement(), int_idDetSol);
                
                int int_estatus = Estatus.statusSiguiente(conn.createStatement(), int_accion, str_perfil, bool_director, bol_VPO );
                String str_estatus = Estatus.codigoStatusSiguiente(int_accion, str_perfil, bool_director, bol_VPO);
                boolean bol_autorizado = str_estatus.equals("AUTORIZADA");
                String str_nomina = usr_logeado.getStr_nomina();
                String str_correo = usr_logeado.getStr_correo();

                ServletContext contexto = session.getServletContext();
                Map map_param = new HashMap();
                map_param.put("mailAut", contexto.getInitParameter("mailAut"));
                map_param.put("passwordFrom", contexto.getInitParameter("passwordFrom"));
                map_param.put("mailFrom", contexto.getInitParameter("mailFrom"));
                map_param.put("host", contexto.getInitParameter("host"));

                (new Solicitud()).autorizarDetalleSolicitud( conn, conn_informix, int_idDetSol, int_estatus, bol_autorizado, map_param, str_perfil, str_nomina, str_correo );
                
            }

            str_mensaje = "Se "+(int_accion==1?"rechazaron":"autorizaron")+" con exito las solicitudes seleccionadas";
        } catch (Exception e) {
        	e.printStackTrace();
            str_mensaje = " Internal Error : "+e.getMessage()+" ";
        } finally {
            try{
                conn.close();
                conn_informix.close();
            }catch(Exception e){}
        }
        request.setAttribute("mensaje", str_mensaje);
        return mapping.findForward("accion_ejecutada_solicitud");
    }
    
    
    /**
     * Valida la sesion
     * 
     * @author  Fernando Celis
     * @param request
     * @return boolean
     * @throws Exception
     */
    public boolean validaSesion(HttpServletRequest request) throws
            Exception {
        HttpSession sesion = request.getSession();
        if (sesion == null)
            return false;
        else
        if (sesion.getAttribute("usuario") == null)
            return false;
        else
            return true;
    }
    
    
    /**
     * 
     * @author Fernando Celis
     * @param actionMapping
     * @param actionForm
     * @param servletRequest
     * @param response
     * @return ActionForward
     * @throws Exception
     */
    public ActionForward listado(ActionMapping actionMapping, ActionForm actionForm,
                                         HttpServletRequest servletRequest,
                                         HttpServletResponse response) throws
            Exception {
    	if (!validaSesion(servletRequest))return actionMapping.findForward("sesionInvalida");
    	
        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource)context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        ListadoActionForm listadoActionForm = (ListadoActionForm) actionForm;

        ArrayList arr_registros, arr_estatus, arr_perfilGte, arr_perfilCoord, arr_perfilEjec;

        String str_estatus =  listadoActionForm.getStr_estatus();
        String str_cliente =  listadoActionForm.getStr_cliente();
        String str_prod    =  listadoActionForm.getStr_producto();
        String str_coord   =  listadoActionForm.getStr_coord();
        String str_gerente =  listadoActionForm.getStr_gerente();
        String str_ejecutivo     =  listadoActionForm.getStr_ejecutivo();
        int int_orden = listadoActionForm.getInt_orden();
        boolean bool_asc   =  listadoActionForm.getBool_asc();

        if(int_orden==0)
           int_orden = 1;

        SortedMap map = new TreeMap();

        HttpSession session = servletRequest.getSession();
        Usuario usr = (Usuario)session.getAttribute("usuario");

        if(usr == null){
            stmt.close();
            conn.close();
            return actionMapping.findForward("sesionInvalida");
        }


        if( listadoActionForm.getStr_estatus() == null ){
            if( usr.getStr_perfilActual().equals(Usuario.EJECUTIVO) || usr.getStr_perfilActual().equals(Usuario.COORDINADOR) || usr.getStr_perfilActual().equals(Usuario.ADMINISTRADOR) ){
                str_estatus = "PENGTE";
            }else if( usr.getStr_perfilActual().equals(Usuario.GERENTE) ){
                str_estatus = "PENGTE";
            }else if( usr.getStr_perfilActual().equals(Usuario.DIRECTOR) ){
                str_estatus = "PENDIR";
            }else if( usr.getStr_perfilActual().equals(Usuario.VPO) ){
                str_estatus = "PENVPO";
            }
            listadoActionForm.setStr_estatus(str_estatus);
        }

        map.put("str_estatus", str_estatus );
        map.put("str_cliente", str_cliente );
        map.put("str_prod"   , str_prod );
        map.put("str_coord"  , str_coord );
        map.put("str_gerente", str_gerente );
        map.put("str_ejecutivo"    , str_ejecutivo );

        arr_registros   =  usr.listado(conn, "", map, int_orden, bool_asc);
        arr_estatus     =  usr.listarEstatus(conn, usr.getStr_perfilActual());

        arr_perfilGte   =  usr.listarGerente(conn, Usuario.GERENTE);
        arr_perfilCoord =  usr.listarCoordinador(conn, Usuario.COORDINADOR);
        arr_perfilEjec =  usr.listarPerfil(conn, Usuario.EJECUTIVO);

        // Atributo de resultado de listado
        servletRequest.setAttribute("listado" ,arr_registros);

        // Atributos de combobox
        servletRequest.setAttribute("estatus" ,arr_estatus);
        servletRequest.setAttribute("perfilGte" ,arr_perfilGte);
        servletRequest.setAttribute("perfilCoord" ,arr_perfilCoord);
        servletRequest.setAttribute("perfilEjec" ,arr_perfilEjec);

        // Atributos para links de encabezado de columna
        servletRequest.setAttribute("codCliente", usr.ordenarColumna(bool_asc, int_orden, 1, map));
        servletRequest.setAttribute("descrCli", usr.ordenarColumna(bool_asc, int_orden, 2, map));
        servletRequest.setAttribute("codProd", usr.ordenarColumna(bool_asc, int_orden, 3, map));
        servletRequest.setAttribute("descrPro", usr.ordenarColumna(bool_asc, int_orden, 4, map));
        servletRequest.setAttribute("fecha", usr.ordenarColumna(bool_asc, int_orden, 5,map));
        servletRequest.setAttribute("status", usr.ordenarColumna(bool_asc, int_orden, 6, map));
        
        conn.close();

        return actionMapping.findForward("listado");
    }

    
    /**
     * Guarda Cliente-Subsegmento en Soliciud por Grupo
     * 
     * @author  Fernando Celis
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return ActionForward
     * @throws Exception
     */
    public ActionForward guardarClientes(ActionMapping mapping, ActionForm form,
                                         HttpServletRequest request,
                                         HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        ArrayList arrL_clientes = new ArrayList();
        ArrayList arrL_codgoCli = new ArrayList();
        ArrayList arrL_subsegmentos = new ArrayList();
        ArrayList arrL_clienteSub = new ArrayList();
        HttpSession session = request.getSession();

        String str_codigoGrupo = request.getParameter("id_grupo");
        String[] strs_clientes = request.getParameterValues("str_agregado");
        //String[] str_codgoCli = request.getParameterValues("str_codgoCli");
        String[] str_codgoCli = request.getParameterValues("str_agregado");
        String[] str_descripcionCli = request.getParameterValues("str_descripcionCli");
        String[] strs_subsegmentos = request.getParameterValues("str_subsegmento");

        // System.out.println(strs_clientes.length);
       
        
        for(int int_i = 0; int_i < strs_clientes.length ; int_i++){
            //System.out.println(strs_clientes[int_i]);
            for(int int_j = 0; int_j < str_codgoCli.length ; int_j++){
                //System.out.println(str_codgoCli[int_j]);
                if(strs_clientes[int_i].equals(str_codgoCli[int_j])){
                    //System.out.println(str_codgoCli[int_j]+ "  " + strs_clientes[int_i]);
                    arrL_codgoCli.add(str_codgoCli[int_j]);
                    arrL_subsegmentos.add(strs_subsegmentos[int_j]);
                    //System.out.println(str_codgoCli[int_j]+ "  " + strs_clientes[int_i] +"  " +strs_subsegmentos[int_j]);

                    ClienteSegmento cliente = new ClienteSegmento();
                    cliente.setStr_cliente(str_codgoCli[int_j]);
                    cliente.setStr_descCliente(str_descripcionCli[int_j]);
                    cliente.setStr_codigoProd(null);
                    cliente.setStr_descProd(null);
                    cliente.setStr_segmento(strs_subsegmentos[int_j]);
                    cliente.setStr_descSegmento(null);

                    // System.out.println(strs_subsegmentos[int_j]);
                    
                    arrL_clientes.add(str_codgoCli[int_j]);
                    arrL_clienteSub.add(cliente);
                }
            }
        }
        session.setAttribute("arrL_clienteSub", arrL_clienteSub);
        session.setAttribute("codigoGrupo", str_codigoGrupo);
        session.setAttribute("clientes_grupo", arrL_clientes);
        session.setAttribute("subsegmentos_grupo", arrL_subsegmentos);
        request.setAttribute("mensaje",  getResources(request).getMessage("GroupClient.Message.SavedSuccessful"));

        return mapping.findForward("accion_ejecutada");
    }

    /***
     * Listado de Clientes por Grupo en soliciud por Grupo
     * @author  Fernando Celis
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listadoClientesGrupo(ActionMapping mapping,
                                              ActionForm form,
                                              HttpServletRequest request,
                                              HttpServletResponse response) throws
            Exception {
    	if (!validaSesion(request)) return mapping.findForward("sesionInvalida");
    	
        HttpSession session = request.getSession();

        String str_codigoGrupo = request.getParameter("id_grupo");
        String str_codigoProducto = request.getParameter("hdn_producto");
        boolean bol_nvo = request.getParameter("hdn_nvo") != null;

        if (bol_nvo && !str_codigoGrupo.equals(session.getAttribute("codigoGrupo"))){
            session.removeAttribute("clientes_grupo");
            session.removeAttribute("subsegmentos_grupo");
        }
        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        GrupoBeanComb grupo = new GrupoBeanComb(conn.createStatement(), str_codigoGrupo);

        request.setAttribute("subsegmentos", Subsegmento.listado(conn.createStatement()));
        request.setAttribute("hdn_producto", str_codigoProducto);
        request.setAttribute("GrupoCliente", grupo);
        // level 5 2009 02 03
        //request.setAttribute("ClientesXGrupo", grupo.clientes(conn.createStatement(), (ArrayList)session.getAttribute("clientes_grupo"), ( (Usuario)request.getSession().getAttribute("usuario") ).getInt_ruta(), str_codigoProducto ) );
        request.setAttribute("ClientesXGrupo", grupo.clientes(conn.createStatement(), (ArrayList)session.getAttribute("clientes_grupo"), ( (Usuario)request.getSession().getAttribute("usuario") ).getStr_ruta(), str_codigoProducto ) );
        conn.close();

        return mapping.findForward("clientesGrupo");
    }

    
    /***
     * Listado de clientes para Solicitud por cliente
     * @author Fernando Celis
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listadoClienteCrearSolicitud(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        ClienteCForm cliente = (ClienteCForm) form;
        if (!validaSesion(request)) return mapping.findForward("sesionInvalida");

        String str_cliente;
        if( request.getParameter("hdn_buscar")!=null ){
            str_cliente = cliente.getStr_search();
        }else{
            str_cliente = "null";
        }
        Usuario usr_logeado = (Usuario)request.getSession().getAttribute("usuario");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();

        // level 5 2009 02 03
        //request.setAttribute("Cliente", ClienteBean.clientes(conn.createStatement(), str_cliente, usr_logeado.getInt_ruta()));
        request.setAttribute("Cliente", ClienteBean.clientes(conn.createStatement(), str_cliente, usr_logeado.getStr_ruta()));

        conn.close();
        return mapping.findForward("listadoClienteCrearSolicitud");
    }

    /***
     * Listado de grupo por Vendedor - Ruta
     *  @author Fernando Celis
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listarGrupo(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {

        if (!validaSesion(request)) return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        ArrayList arr_grupo = new ArrayList();
        try {
            HttpSession session = request.getSession();
            Usuario usr = (Usuario) session.getAttribute("usuario");

            if (usr == null) {
                if (conn != null && !conn.isClosed())
                    conn.close();
                return mapping.findForward("sesionInvalida");
            }

            Grupo grupo = new Grupo();
            arr_grupo = grupo.listarGrupo(stmt, usr.getStr_nomina());

            request.setAttribute("arr_grupo", arr_grupo);

            return mapping.findForward("listadogrp");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }

    }

    
    /**
     * Inserta GrupoProducto
     * @author Fernando Celis
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward insertarGrupo(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request)) return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Grupo grupo = new Grupo();
            GrupoForm grupoForm = (GrupoForm) form;

            boolean bool_status;
            String str_descGrupo = grupoForm.getStr_descGrupo();
            String str_lineaGrupo = grupoForm.getStr_lineaGrupo();

            bool_status = grupo.insertarGrupo(conn, str_descGrupo, str_lineaGrupo, ( (Usuario)request.getSession().getAttribute("usuario") ).getStr_nomina());

            if (bool_status) {

                request.setAttribute("msg", "");
                return mapping.findForward("messageWindow");
            } else {
                request.setAttribute("msg", "");
                return mapping.findForward("messageWindow");
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    /**
     * Elimina Grupo Producto
     * 
     * @author Fernando Celis
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward eliminarGrupo(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Grupo grupo = new Grupo();
            int id_grupo = Integer.parseInt((String) request.getParameter("id_grupo"));
            grupo.eliminarGrupo(conn, id_grupo);

            request.setAttribute("msg", "");
            return mapping.findForward("messageWindow");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * Actualiza Datos GrupoProducto 
     *  
     * @author Fernando Celis
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward actualizarGrupo(ActionMapping mapping,
                                         ActionForm form,
                                         HttpServletRequest request,
                                         HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Grupo grupo = new Grupo();
        GrupoForm grupoForm = (GrupoForm) form;

        try {
            boolean bool_status;
            String str_idGrupo = grupoForm.getStr_idGrupo();
            String str_descGrupo = grupoForm.getStr_descGrupo();
            String str_lineaGrupo = grupoForm.getStr_lineaGrupo();

            bool_status = grupo.actualizarGrupo(conn, str_idGrupo,
                                                str_descGrupo, str_lineaGrupo);

            if (bool_status) {
                request.setAttribute("msg", "");
                return mapping.findForward("messageWindow");
            } else {
                request.setAttribute("msg", "");
                return mapping.findForward("messageWindow");
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            conn.close();
        }
    }

    /**
     * Muestra Datos para edicion de GrupoProducto
     * 
     * @author Fernando Celis
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editarGrupo(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        GrupoForm grupoForm = (GrupoForm) form;
        ArrayList arr_grupo;
        int int_idGrupo;

        try {
            Grupo grupo = new Grupo();
            String str_idGrupo = "";
            String str_descGrupo = "";
            String str_lineaGrupo = "";

            if (request.getParameter("id_grupo") != null) {
                int_idGrupo = Integer.parseInt((String) request.getParameter(
                        "id_grupo"));

                arr_grupo = grupo.muestraGrupo(stmt, int_idGrupo);

                str_idGrupo = ((String) arr_grupo.get(0));
                str_descGrupo = ((String) arr_grupo.get(1));
                str_lineaGrupo = ((String) arr_grupo.get(2));

                grupoForm.setStr_idGrupo(str_idGrupo);
                grupoForm.setStr_descGrupo(str_descGrupo);
                grupoForm.setStr_lineaGrupo(str_lineaGrupo);
            }
            // Listado de Linea de Producto
            ArrayList arr_linea = grupo.listarLinea(stmt, str_lineaGrupo);
            request.setAttribute("arr_linea", arr_linea);

            return mapping.findForward("editargrupo");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * 
     * @author Fernando Romero
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listGrupoProd(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        ArrayList arr_grupoProd;

        try {
            Grupo grupo = new Grupo();
            Map map_insertarUrl = new HashMap();
            String str_idGrupo = request.getParameter("id_grupo");
            if (str_idGrupo == null)
                str_idGrupo = "";

            arr_grupoProd = grupo.listadoGrpProd(stmt, str_idGrupo);

            map_insertarUrl.put("action", grupo.LISTARPROD);
            map_insertarUrl.put("id_grupo", str_idGrupo);

            request.setAttribute("map_insertarUrl", map_insertarUrl);
            request.setAttribute("arr_grupoProd", arr_grupoProd);

            return mapping.findForward("listadoGrpProd");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listProducto(ActionMapping mapping,
                                      ActionForm form,
                                      HttpServletRequest request,
                                      HttpServletResponse response) throws
            Exception {
        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        ArrayList arr_producto;
        try {
            if (!validaSesion(request))
                return mapping.findForward("sesionInvalida");

            ProductoForm producto_form = ((ProductoForm) form);
            String str_idGrupo = request.getParameter("id_grupo");
            producto_form.setId_grupo(str_idGrupo);
            String str_search = producto_form.getStr_search();
            if (str_search == null)
                str_search = "";
            Grupo grupo = new Grupo();

            arr_producto = grupo.listarProducto(stmt, str_idGrupo, str_search);
            request.setAttribute("arr_producto", arr_producto);
            return mapping.findForward("listadoProd");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward insertProducto(ActionMapping mapping,
                                        ActionForm form,
                                        HttpServletRequest request,
                                        HttpServletResponse response) throws
            Exception {
        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");
        try{
            Grupo grupo = new Grupo();
            ProductoForm producto_form = ((ProductoForm) form);
            String str_idGrupo = producto_form.getId_grupo();
            String[] str_select = producto_form.getStr_select();

            grupo.insertaProducto(stmt, str_select, str_idGrupo);

            return mapping.findForward("insertaProd");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward deleteProducto(ActionMapping mapping,
                                        ActionForm form,
                                        HttpServletRequest request,
                                        HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            String str_codigoProd = request.getParameter("codigo_prod");
            Grupo grupo = new Grupo();
            grupo.eliminaProducto(stmt, str_codigoProd);

            return mapping.findForward("deleteProd");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }



    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward muestraParametro(ActionMapping mapping,
                                          ActionForm form,
                                          HttpServletRequest request,
                                          HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {

            return mapping.findForward("");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
    
    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editaParametro(ActionMapping mapping,
                                        ActionForm form,
                                        HttpServletRequest request,
                                        HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        final String[] str_perfil = {"Gerente", "Director", "VPO"};

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        ParametroForm param_form = (ParametroForm) form;
        Parametro param = new Parametro();

        ArrayList arr_user = param.muestraUsuario(stmt, "");
        Map map;

        try{
            map = param.muestraParametro(stmt);

            param_form.setStr_idParam((String) map.get("str_idParam"));
            param_form.setStr_avisoExp((String) map.get("str_avisoExp"));
            param_form.setStr_diasExp((String) map.get("str_diasExp"));
            param_form.setStr_directorParam((String) map.get("str_directorParam"));
            param_form.setStr_gerenteParam((String) map.get("str_gerenteParam"));
            param_form.setStr_vpoParam((String) map.get("str_vpoParam"));
            param_form.setStr_estructuraParam((String) map.get("str_estructuraParam"));
            param_form.setStr_dirBackup((String) map.get("str_dirBackup"));
            param_form.setStr_dirBackupN((String) map.get("str_dirBackupN"));
            param_form.setStr_vpoBackup((String) map.get("str_vpoBackup"));
            param_form.setStr_vpoBackupN((String) map.get("str_vpoBackupN"));
            param_form.setBool_autorizaVPO(((Boolean) map.get("bool_autorizaVPO")).booleanValue());

            for (int int_i = 0; int_i < str_perfil.length; int_i++) {
                arr_user = param.muestraUsuario(stmt, str_perfil[int_i]);
                request.setAttribute(str_perfil[int_i], arr_user);
            }

            return mapping.findForward("editaParam");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward actualizaParametro(ActionMapping mapping,
                                            ActionForm form,
                                            HttpServletRequest request,
                                            HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        ParametroForm param_form = (ParametroForm) form;
        Parametro parametro = new Parametro();
        Map map_param = new HashMap();

        try {

            map_param.put("id_param", param_form.getStr_idParam());
            map_param.put("avisoexp_param", param_form.getStr_avisoExp());
            map_param.put("diasexp_param", param_form.getStr_diasExp());
            map_param.put("director_param", param_form.getStr_directorParam());
            map_param.put("gerente_param", param_form.getStr_gerenteParam());
            map_param.put("vpo_param", param_form.getStr_vpoParam());
            map_param.put("estructura_param", param_form.getStr_estructuraParam());
            map_param.put("dirbackup_param", param_form.getStr_dirBackup());
            map_param.put("vpobackup_param", param_form.getStr_vpoBackup());
            map_param.put("bool_autorizaVPO", new Boolean(param_form.getBool_autorizaVPO()));

            parametro.actualizaParametro(stmt, map_param);
            request.setAttribute("msg", "");
            return mapping.findForward("messageWindow");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
    

    /** 
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listadoCliC(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            ArrayList arr_cliente = new ArrayList();
            ClienteCritico cliente = new ClienteCritico();
            arr_cliente = cliente.listadoCliC(stmt);

            request.setAttribute("arr_cliente", arr_cliente);

            return mapping.findForward("listadoClienteC");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listadoCli(ActionMapping mapping,
                                    ActionForm form,
                                    HttpServletRequest request,
                                    HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        try {
            ClienteCForm clienteform = (ClienteCForm) form;
            String str_search;
            str_search = clienteform.getStr_search();

            if (str_search == null)
                str_search = "";

            ArrayList arr_cliente;
            ClienteCritico cliente = new ClienteCritico();

            arr_cliente = cliente.listadoCli(stmt, str_search);

            request.setAttribute("arr_cliente", arr_cliente);

            return mapping.findForward("listadoCliente");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward insertaCliC(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            String str_idClienteC = request.getParameter("id_clienteC");
            ClienteCritico cliente = new ClienteCritico();

            cliente.insertaClienteC(stmt, str_idClienteC);
            request.setAttribute("msg", "");

            return mapping.findForward("insertaCliente");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward eliminaCliC(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            String str_idClienteC = request.getParameter("id_clienteC");
            ClienteCritico cliente = new ClienteCritico();

            cliente.eliminarClienteC(stmt, str_idClienteC);

            return mapping.findForward("eliminaCliente");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
    
    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listarClase(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            ArrayList arr_clase;
            ClaseProducto clase = new ClaseProducto();

            arr_clase = clase.listarClase(stmt);
            request.setAttribute("arr_clase", arr_clase);

            return mapping.findForward("listadoClaseP");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editarClase(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();
        try {
            ClasePForm claseForm = (ClasePForm) form;
            ClaseProducto clase = new ClaseProducto();
            String str_idclProd = request.getParameter("str_idclProd");
            ArrayList arr_gerente;
            Map map;

            map = clase.editarClase(stmt, str_idclProd);
            claseForm.setStr_idclProd((String) map.get("str_idclProd"));
            claseForm.setStr_codigoclProd((String) map.get("str_codigoclProd"));
            claseForm.setStr_codigoGte((String) map.get("str_codigoGte"));
            claseForm.setStr_nombreUsr((String) map.get("str_nombreUsr"));
            claseForm.setStr_backupGte((String) map.get("str_backupGte"));
            claseForm.setStr_backupGteN((String) map.get("str_backupGteN"));
            claseForm.setStr_responsable((String) map.get("str_responsable"));
            claseForm.setStr_responsableN((String) map.get("str_responsableN"));
            claseForm.setInt_avisoExp(Integer.parseInt((String) map.get("int_avisoExp")));
            claseForm.setInt_diasExp(Integer.parseInt((String) map.get("int_diasExp")));
            claseForm.setInt_desviacion(((Integer) map.get("int_desviacion")).intValue());
            claseForm.setInt_desviacionSup(((Integer) map.get("int_desviacionSup")).intValue());
            claseForm.setBool_autorizaDireccion(((Boolean)map.get("bool_autorizaDireccion")).booleanValue());

            arr_gerente = clase.listarGerente(stmt);

            request.setAttribute("arr_gerente", arr_gerente);

            return mapping.findForward("editarClaseP");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward actualizarClase(ActionMapping mapping,
                                         ActionForm form,
                                         HttpServletRequest request,
                                         HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            ClasePForm claseForm = (ClasePForm) form;
            ClaseProducto clase = new ClaseProducto();

            Map map = new HashMap();
            map.put("str_idclProd", claseForm.getStr_idclProd());
            map.put("str_codigoclProd", claseForm.getStr_codigoclProd());
            map.put("str_codigoGte", claseForm.getStr_codigoGte());
            map.put("str_backupGte", claseForm.getStr_backupGte());
            map.put("str_responsable", claseForm.getStr_responsable());
            map.put("int_avisoExp", ""+claseForm.getInt_avisoExp());
            map.put("int_diasExp", ""+claseForm.getInt_diasExp());
            map.put("int_desviacion", new Integer(claseForm.getInt_desviacion()));
            map.put("int_desviacionSup", new Integer(claseForm.getInt_desviacionSup()));
            map.put("bool_autorizaDireccion", new Boolean(claseForm.getBool_autorizaDireccion()));

            clase.actualizarClase(stmt, map);

            return mapping.findForward("actualizarClaseP");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
    
    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward buscarUsuario(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            ClienteCForm usuarioForm = (ClienteCForm) form;
            ClaseProducto clase = new ClaseProducto();
            ArrayList arr_usuario;

            Map map_form = new HashMap();

            map_form.put("nomina", request.getParameter("nomina"));
            map_form.put("nombre", request.getParameter("nombre"));

            request.setAttribute("map_form", map_form);

            String str_search = usuarioForm.getStr_search();
            if (str_search == null)
                str_search = "";

            arr_usuario = clase.listarUsuario(stmt, str_search);
            request.setAttribute("arr_usuario", arr_usuario);

            return mapping.findForward("listarUser");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listEstatus(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Estatus estatus = new Estatus();
            ArrayList arr_estatus = estatus.listarEstatus(stmt, "");
            request.setAttribute("arr_estatus", arr_estatus);

            return mapping.findForward("listaEstatus");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward insertEstatus(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            String str_deNueva = request.getParameter("rd_createSol");
            if (str_deNueva == null)
                str_deNueva = "0";

            EstatusForm form_estatus = (EstatusForm) form;
            Estatus estatus = new Estatus();
            Map mapa_estatus = new HashMap();

            mapa_estatus.put("str_codEstatus", form_estatus.getStr_codEstatus());
            mapa_estatus.put("str_descEstatus", form_estatus.getStr_descEstatus());
            mapa_estatus.put("str_deNueva", str_deNueva);
            estatus.insertarEstatus(stmt, mapa_estatus);

            return mapping.findForward("insertaEstatus");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward updateEstatus(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            String str_deNueva = request.getParameter("rd_createSol");
            if (str_deNueva == null)
                str_deNueva = "0";

            EstatusForm form_estatus = (EstatusForm) form;
            Estatus estatus = new Estatus();
            Map mapa_estatus = new HashMap();

            mapa_estatus.put("str_idEstatus", form_estatus.getStr_idEstatus());
            mapa_estatus.put("str_codEstatus", form_estatus.getStr_codEstatus());
            mapa_estatus.put("str_descEstatus", form_estatus.getStr_descEstatus());
            mapa_estatus.put("str_deNueva", str_deNueva);

            estatus.actualizaEstatus(stmt, mapa_estatus);

            return mapping.findForward("actualizaEstatus");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward deleteEstatus(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Estatus estatus = new Estatus();
            String str_idEstatus = request.getParameter("str_idEstatus");
            if (str_idEstatus == null)
                str_idEstatus = "";
            estatus.eliminarEstatus(stmt, str_idEstatus);

            return mapping.findForward("borraEstatus");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editEstatus(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            EstatusForm form_estatus = (EstatusForm) form;
            Estatus estatus = new Estatus();

            String str_idEstatus = request.getParameter("str_idEstatus");

            if (str_idEstatus != null) {
                ArrayList arr_edit = estatus.listarEstatus(stmt, str_idEstatus);
                Iterator it = arr_edit.iterator();
                EstatusBean bn_estatus = null;
                while (it.hasNext()) {
                    bn_estatus = (EstatusBean) it.next();
                }
                form_estatus.setStr_idEstatus(bn_estatus.getStr_idEstatus());
                form_estatus.setStr_codEstatus(bn_estatus.getStr_codEstatus());
                form_estatus.setStr_descEstatus(bn_estatus.getStr_descEstatus());
            }

            return mapping.findForward("editaEstatus");

        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listarMatriz(ActionMapping mapping,
                                      ActionForm form,
                                      HttpServletRequest request,
                                      HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            MatrizCorreo matriz = new MatrizCorreo();
            ArrayList arr_matriz = matriz.listarMatriz(stmt);

            request.setAttribute("arr_matriz", arr_matriz);

            return mapping.findForward("listarMatriz");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editarMatriz(ActionMapping mapping,
                                      ActionForm form,
                                      HttpServletRequest request,
                                      HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            MatrizCorreoForm frm = (MatrizCorreoForm) form;
            ArrayList arr_perfil;
            ArrayList arr_otro;
            String str_idEstatus = request.getParameter("str_idEstatus");
            String str_idDirigido = request.getParameter("str_idDirigido");

            MatrizCorreo matriz = new MatrizCorreo();
            arr_perfil = matriz.listarPerfiles(stmt, str_idEstatus, str_idDirigido);
            arr_otro   = matriz.listarOtro(stmt, str_idEstatus, str_idDirigido);

            Iterator it = arr_otro.iterator();

            if (it.hasNext()) {
                OtroCorreoBean bn_otro = (OtroCorreoBean) it.next();
                frm.setStr_descOtro(bn_otro.getStr_nombreUsuario());
                frm.setStr_nomina(bn_otro.getStr_nominaUsuario());
            }

            request.setAttribute("arr_perfil", arr_perfil);

            return mapping.findForward("editarMatriz");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward actualizarMatriz(ActionMapping mapping,
                                          ActionForm form,
                                          HttpServletRequest request,
                                          HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            MatrizCorreo matriz = new MatrizCorreo();
            MatrizCorreoForm frm_matriz = (MatrizCorreoForm) form;
            String str_nomina = frm_matriz.getStr_nomina();
            String str_idEstatus = frm_matriz.getStr_idEstatus();
            String str_idDirigido = frm_matriz.getStr_idDirigido();
            String[] str_idPerfilS = request.getParameterValues("str_idPerfilS");

            matriz.actualizarPerfil(stmt, str_idPerfilS, str_idEstatus, str_idDirigido);
            matriz.actualizarOtro(stmt, str_idEstatus, str_idDirigido, str_nomina);

            return mapping.findForward("actualizarMatriz");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
    

    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listarProducto(ActionMapping mapping,
                                        ActionForm form,
                                        HttpServletRequest request,
                                        HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Producto producto = new Producto();
            ArrayList arrL_producto;

            String str_search = request.getParameter("str_search");
            if (str_search == null) str_search = "";

            arrL_producto = producto.listarProducto(stmt, str_search);
            request.setAttribute("arrL_producto", arrL_producto);

            return mapping.findForward("listarProducto");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


   
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward listarPrecioL(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            ArrayList arrL_segmento;
            ArrayList arrL_precios;
            PrecioLista precio = new PrecioLista();
            DynaActionForm frm_precio = (DynaActionForm) form;

            String str_idGrupo = (String) frm_precio.get("str_idGrupo");
            request.setAttribute("str_idGrupo", str_idGrupo);

            arrL_segmento = precio.listarSegmento(stmt);
            request.setAttribute("arrL_segmento", arrL_segmento);

            if( str_idGrupo != null && !str_idGrupo.equals("") ){
                arrL_precios = precio.listarPrecios(stmt, Integer.parseInt(str_idGrupo));
                request.setAttribute("arrL_precios", arrL_precios);
            }

            return mapping.findForward("listarPrecioL");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward editarPrecioL(ActionMapping mapping,
                                       ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            PrecioLista precio = new PrecioLista();
            PrecioListaEd frm_precio = (PrecioListaEd) form;

            ArrayList arrL_segemento;
            ArrayList arrL_grupo;

            String str_idGrupo = request.getParameter("str_idGrupo");
            String str_rangoMin = request.getParameter("str_rangoMin");
            String str_rangoMax = request.getParameter("str_rangoMax");

            arrL_segemento = precio.editarSegmentoPrecio(stmt, str_idGrupo,
                    str_rangoMin, str_rangoMax);
            arrL_grupo = precio.listarGrupo(stmt, str_idGrupo);

            frm_precio.setStr_rangoMin(str_rangoMin == null ? "" : str_rangoMin);
            frm_precio.setStr_rangoMax(str_rangoMax == null ? "" : str_rangoMax);
            frm_precio.setStr_rangoMaxAnt(frm_precio.getStr_rangoMax());
            frm_precio.setStr_rangoMinAnt(frm_precio.getStr_rangoMin());
            if(str_rangoMin == null && str_rangoMax == null)
                request.setAttribute("bool_action", String.valueOf(false));
            request.setAttribute("arrL_segemento", arrL_segemento);
            request.setAttribute("arrL_grupo", arrL_grupo);

            return mapping.findForward("editar_PrecioL");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward insertarPrecioL(ActionMapping mapping,
                                         ActionForm form,
                                         HttpServletRequest request,
                                         HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            PrecioLista precio = new PrecioLista();
            PrecioListaEd frm_precio = (PrecioListaEd) form;

            String str_idGrupo = frm_precio.getStr_idGrupo();
            String str_rangoMin = frm_precio.getStr_rangoMin();
            String str_rangoMax = frm_precio.getStr_rangoMax();
            String[] str_codigoSeg = frm_precio.getStr_codigoSeg();
            String[] str_cantidadPrecio = frm_precio.getStr_cantidadPrecio();

            boolean bool_valida = precio.validaRango(stmt, str_idGrupo, str_rangoMin, str_rangoMax);
            if(!bool_valida){
                ArrayList arrL_segemento;
                ArrayList arrL_grupo;

                arrL_segemento = precio.editarSegmentoPrecio(stmt, str_idGrupo,
                        null, null);
                arrL_grupo = precio.listarGrupo(stmt, str_idGrupo);

                frm_precio.setStr_rangoMin(str_rangoMin == null ? "" : str_rangoMin);
                frm_precio.setStr_rangoMax(str_rangoMax == null ? "" : str_rangoMax);

                request.setAttribute("arrL_segemento", arrL_segemento);
                request.setAttribute("arrL_grupo", arrL_grupo);

                request.setAttribute("bool_valida", String.valueOf(bool_valida));
                return mapping.findForward("editar_PrecioL");
            }

            precio.insertarPrecioL(stmt, str_idGrupo, str_rangoMin,
                                       str_rangoMax, str_codigoSeg,
                                       str_cantidadPrecio);

            return mapping.findForward("actualizarPrecio");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward actualizarPrecioL(ActionMapping mapping,
                                           ActionForm form,
                                           HttpServletRequest request,
                                           HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            PrecioLista precio = new PrecioLista();
            PrecioListaEd frm_precio = (PrecioListaEd) form;

            String str_idGrupo = frm_precio.getStr_idGrupo();
            String str_rangoMin = frm_precio.getStr_rangoMin();
            String str_rangoMax = frm_precio.getStr_rangoMax();
            String str_rangoMinAnt = frm_precio.getStr_rangoMinAnt();
            String str_rangoMaxAnt = frm_precio.getStr_rangoMaxAnt();
            String[] str_codigoSeg = frm_precio.getStr_codigoSeg();
            String[] str_cantidadPrecio = frm_precio.getStr_cantidadPrecio();

            boolean bool_valida = precio.validaRango(stmt, str_idGrupo, str_rangoMin, str_rangoMax, str_rangoMinAnt, str_rangoMaxAnt);
            if(!bool_valida){
                ArrayList arrL_segemento;
                ArrayList arrL_grupo;

                arrL_segemento = precio.editarSegmentoPrecio(stmt, str_idGrupo,
                        null, null);
                arrL_grupo = precio.listarGrupo(stmt, str_idGrupo);

                frm_precio.setStr_rangoMin(str_rangoMin == null ? "" : str_rangoMin);
                frm_precio.setStr_rangoMax(str_rangoMax == null ? "" : str_rangoMax);

                request.setAttribute("arrL_segemento", arrL_segemento);
                request.setAttribute("arrL_grupo", arrL_grupo);

                request.setAttribute("bool_valida", String.valueOf(bool_valida));

                return mapping.findForward("editar_PrecioL");
            }

            precio.actualizarPrecioL(stmt, str_idGrupo, str_rangoMin,
                                     str_rangoMax, str_codigoSeg,
                                     str_cantidadPrecio, str_rangoMinAnt, str_rangoMaxAnt);

            return mapping.findForward("actualizarPrecio");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }


    /**
     * 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward buscarGrupo(ActionMapping mapping,
                                     ActionForm form,
                                     HttpServletRequest request,
                                     HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Grupo grupo = new Grupo();
            ArrayList arrL_grupo;
            DynaActionForm frm_search = (DynaActionForm) form;
            frm_search.reset(mapping, request);

            String str_search = (String) frm_search.get("str_search");
            arrL_grupo = grupo.buscarGrupo(stmt, str_search, ((Usuario)request.getSession().getAttribute("usuario") ).getStr_nomina());

            request.setAttribute("arrL_grupo", arrL_grupo);

            return mapping.findForward("buscarGrupo");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }

    
    /**
     * Listado Matriz de Notificacion 
     * 
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
   public ActionForward listarNotificacion(ActionMapping mapping,
                                           ActionForm form,
                                           HttpServletRequest request,
                                           HttpServletResponse response) throws
           Exception {
       if (!validaSesion(request))
          return mapping.findForward("sesionInvalida");

       InitialContext context = new InitialContext();
       DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
       Connection conn = dataSrc.getConnection();
       Statement stmt = conn.createStatement();

       try{
           ArrayList arrL_matriz;
           Notificacion notificacion = new Notificacion();

           arrL_matriz = notificacion.listarMatriz(stmt);
           request.setAttribute("arrL_matriz", arrL_matriz);

           return mapping.findForward("listarNotificacion");
       }catch(Exception ex){
           throw ex;
       }finally{
           stmt.close();
           conn.close();
       }
   }

   
   /**
    * 
    * 
    * @param mapping
    * @param form
    * @param request
    * @param response
    * @return
    * @throws Exception
    */
   public ActionForward editarNotificacion(ActionMapping mapping,
                                           ActionForm form,
                                           HttpServletRequest request,
                                           HttpServletResponse response) throws
           Exception {

       if (!validaSesion(request))
           return mapping.findForward("sesionInvalida");

       InitialContext context = new InitialContext();
       DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
       Connection conn = dataSrc.getConnection();
       Statement stmt = conn.createStatement();

       try {
           DynaActionForm frm_matriz = (DynaActionForm) form;

           ArrayList arr_perfil;
           ArrayList arr_otro;
           String str_idNotificacion = request.getParameter("str_idNotificacion");
           String str_idDirigido = request.getParameter("str_idDirigido");

           Notificacion notificacion = new Notificacion();
           arr_perfil = notificacion.listarPerfiles(stmt, str_idNotificacion, str_idDirigido);
           arr_otro = notificacion.listarOtro(stmt, str_idNotificacion, str_idDirigido);

           Iterator it = arr_otro.iterator();

           if (it.hasNext()) {
               NotificacionOtroC bn_otro = (NotificacionOtroC) it.next();
               frm_matriz.set("str_idNotificacion", str_idNotificacion);
               frm_matriz.set("str_idDirigido",     str_idDirigido);
               frm_matriz.set("str_descOtro",       bn_otro.getStr_nombreUsuario());
               frm_matriz.set("str_nomina",         bn_otro.getStr_nominaUsuario());
           }
           request.setAttribute("arr_perfil", arr_perfil);

           return mapping.findForward("editarNotificacion");
       }catch(Exception ex){
           throw ex;
       }finally{
           stmt.close();
           conn.close();
       }
   }
   
   /**
    * 
    * 
    * @param mapping
    * @param form
    * @param request
    * @param response
    * @return
    * @throws Exception
    */
   public ActionForward actualizarNotificacion(ActionMapping mapping,
                                          ActionForm form,
                                          HttpServletRequest request,
                                          HttpServletResponse response) throws
            Exception {
        if (!validaSesion(request))
            return mapping.findForward("sesionInvalida");

        InitialContext context = new InitialContext();
        DataSource dataSrc = (DataSource) context.lookup("java:comp/env/jdbc/Eprice");
        Connection conn = dataSrc.getConnection();
        Statement stmt = conn.createStatement();

        try {
            Notificacion matriz = new Notificacion();
            DynaActionForm frm_matriz = (DynaActionForm) form;

            String str_nomina           = (String)frm_matriz.get("str_nomina");
            String str_idNotificacion   = (String)frm_matriz.get("str_idNotificacion");
            String str_idDirigido       = (String)frm_matriz.get("str_idDirigido");
            String[] str_idPerfilS      = request.getParameterValues("str_idPerfilS");

            matriz.actualizarPerfil(stmt, str_idPerfilS, str_idNotificacion, str_idDirigido);
            matriz.actualizarOtro(stmt, str_idNotificacion, str_idDirigido, str_nomina);

            return mapping.findForward("actualizarNotificacion");
        } catch (Exception ex) {
            throw ex;
        } finally {
            stmt.close();
            conn.close();
        }
    }
}
